#!/usr/bin/env python
# coding: utf-8

# In[1]:


import numpy as np
import pandas as pd


# 1、数据加载， pd.read_excel('./18级高一体测成绩汇总.xls')默认加载第一个工作表

# In[2]:


data1=pd.read_excel('./18级高一体测成绩汇总.xls')
data1.head()


# 2、数据加载， pd.read_excel('./18级高一体测成绩汇总.xls',sheet_name = 1)指定加载第二个工作表

# In[3]:


data2=pd.read_excel('18级高一体测成绩汇总.xls',sheet_name = 1)
data2.head()


# 3、评分标准加载，pd.read_excel('./体侧成绩评分表.xls',header = [0,1])，header=[0,1]表示多层列索引

# In[4]:


data3=pd.read_excel('./体侧成绩评分表.xls',header = [0,1])
data3.head()


# 4、数据类型转换
# 
# 男1000米跑，数据类型是str，并且是4’26这种形式，需要变成float类型的值

# In[5]:


data1['男1000米跑']=data1['男1000米跑'].astype('str')
data1['男1000米跑']=data1['男1000米跑'].str.replace("'",'.').map(lambda x:float(x))
data1['男1000米跑']


# 评分标准中男1000米跑和女800米跑的成绩都是4‘10’‘这种形式，需要转化为float类型值

# In[6]:


data3.loc[:,('女800米跑','成绩')]=data3['女800米跑']['成绩'].str.replace("'",".").str.replace('"','').map(lambda x:float(x))
data3.loc[:,('男1000米跑','成绩')]=data3['男1000米跑']['成绩'].str.replace("'",".").str.replace('"','').map(lambda x:float(x))
data3['女800米跑']['成绩']
data3['男1000米跑']['成绩']


# 5、对体测成绩进行分数转换，跑步类（越小越好）；跳远、体前屈（越大越好）
# 
# 使用map、apply、transform方法
# 
# 列索引重排

# In[7]:


for i in ['男跳远','男体前屈','男引体','男肺活量']:
    dt_score=data3[i]['成绩'].dropna().values
    x=dt_score.tolist()
    x.sort()
    x.insert(0,-5)
    x.append(10000)
    dt_fenshu=data3[i].dropna()['分数'].values
    y=dt_fenshu.tolist()
    y.sort()
    y.insert(0,0)
    df=pd.cut(data1[i],bins=x,labels=y,right=False)
    data1[i+'分数']=df
data1.head()


# In[8]:


for i in ['男1000米跑','男50米跑']:
    dt_score=data3[i]['成绩'].dropna().values
    x=dt_score.tolist()
    x.insert(0,0)
    x.append(100)
    dt_fenshu=data3[i].dropna()['分数'].values
    y=dt_fenshu.tolist()
    y.append(0)
    df=pd.cut(data1[i],bins=x,labels=y)
    data1[i+'分数']=df
data1.head()


# In[10]:


data1['BMI']=data1['体重']*10000/(data1['身高']**2)
data1['BMI']=data1['BMI'].round(2)
data1['BMI'].head()


# In[11]:


data_male=data1[['班级','性别','男1000米跑','男1000米跑分数','男50米跑','男50米跑分数','男跳远','男跳远分数','男体前屈','男体前屈分数',
                   '男引体','男引体分数','男肺活量', '男肺活量分数','身高', '体重','BMI']]
data_male.head()


# In[12]:


for i in ['女跳远','女体前屈','女仰卧','女肺活量']:
    dt_score=data3[i]['成绩'].dropna().values
    x=dt_score.tolist()
    x.sort()
    x.insert(0,-5)
    x.append(10000)
    dt_fenshu=data3[i].dropna()['分数'].values
    y=dt_fenshu.tolist()
    y.sort()
    y.insert(0,0)
    df=pd.cut(data2[i],bins=x,labels=y,right=False)
    data2[i+'分数']=df
for i in ['女800米跑','女50米跑']:
    dt_score=data3[i]['成绩'].dropna().values
    x=dt_score.tolist()
    x.insert(0,0)
    x.append(100)
    dt_fenshu=data3[i].dropna()['分数'].values
    y=dt_fenshu.tolist()
    y.append(0)
    df=pd.cut(data2[i],bins=x,labels=y)
    data2[i+'分数']=df
data2['BMI']=data2['体重']*10000/(data2['身高']**2)
data2['BMI']=data2['BMI'].round(2)
data2.head()


# In[13]:


data_female=data2[['班级','性别','女800米跑','女800米跑分数','女50米跑','女50米跑分数','女跳远','女跳远分数','女体前屈','女体前屈分数',
                   '女仰卧','女仰卧分数','女肺活量', '女肺活量分数','身高', '体重','BMI']]
data_female.head()

